﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using AssetManagementSystem.Utils;

namespace AssetManagementSystem.Model.Dao.MSAccess {
    class LoanDAO  : AssetManagementSystem.Model.IDao.ILoanDAO {

        //The MSAccess database connection to use in all data access operations.
        private OleDbConnection dbConn;

        /// <summary>
        /// Constructs a new LoanDAO using a MSAccess database connection string.
        /// </summary>
        /// <param name="connectionString">MSAccess database connection string</param>
        public LoanDAO(string connectionString) {
            //create new database connection
            this.dbConn = new OleDbConnection(connectionString);
            //test the new database connection
            try {
                //make sure the connection can be opened.
                dbConn.Open();
            } catch (Exception e) {
                throw e;
            } finally {
                //close connection if goes went wrong.
                dbConn.Close();
            }
        }

        //====================================================================
        //  CRUD METHODS - CREATE, READ, UPDATE, DELETE
        //====================================================================

        /// <summary>
        /// Create a new loan in the database, from an 'Loan' business object.
        /// </summary>
        /// <param name="loan">The loan object to be stored in the database</param>
        public void CreateLoan(Loan loan) {
            //Make sure the loan assets are available
            foreach (KeyValuePair<string, DateTime> asset in loan.Assets) {
                if (!IsLoanAssetAvailable(asset.Key)) {
                    //cancel loan creation and throw exception if asset unavailable
                    throw new Exception("Asset with ID: " + asset.Key + "is not available, loan could not be created");
                }
            }

            int newId;

            try {
                //store the new loan
                dbConn.Open();
                OleDbCommand oleDbCmd = new OleDbCommand(
                    "INSERT INTO Loan ([StartDate], [DueDate], [StudentID]) " +
                    "VALUES(@StartDate, @DueDate, @StudentID)",
                    dbConn);
                OleDbParameter p1 = oleDbCmd.Parameters.AddWithValue("@StartDate", loan.StartDate);
                p1.OleDbType = OleDbType.Date;
                OleDbParameter p2 = oleDbCmd.Parameters.AddWithValue("@DueDate", loan.DueDate);
                p2.OleDbType = OleDbType.Date;
                oleDbCmd.Parameters.AddWithValue("@StudentID", loan.StudentId);
                //make sure the database changed
                if (oleDbCmd.ExecuteNonQuery() < 1) {
                    throw new Exception("Loan not stored. An unknown error occured");
                }

                //get the id of the new loan
                oleDbCmd.CommandText = "Select @@Identity";
                newId = (int)oleDbCmd.ExecuteScalar();

            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }


            //now store each of the loans assets
            foreach (KeyValuePair<string, DateTime> asset in loan.Assets) {
                StoreLoanAsset(newId, asset.Key);
            }
        }

        /// <summary>
        /// Retrieve a single loan from the database, using its ID as the PKey.
        /// </summary>
        /// <param name="id">The loan's id</param>
        /// <returns>The 'Loan' business object for the requested loan.</returns>
        public Loan GetLoan(int id) {
            Loan loan = null;
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Loan WHERE (ID = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("ID", id);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //if more or less than 1 loan is returned for this id, throw an exception
                if (dataTable.Rows.Count != 1) {
                    throw new Exception(dataTable.Rows.Count.ToString() + " records found for loan " + id.ToString() + " expected 1");
                }
                //create an Loan object for the record returned
                foreach (DataRow loanRow in dataTable.Rows) {
                    loan = new Loan(
                        Convert.ToInt32(loanRow["ID"].ToString()),
                        DateUtil.ValidDate(loanRow["StartDate"].ToString()),
                        DateUtil.ValidDate(loanRow["DueDate"].ToString()),
                        loanRow["StudentID"].ToString(),
                        new Dictionary<string,DateTime>());
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //Get the additional properties of the loan.
            loan.Assets = GetLoanAssets(loan.Id);

            //return the loan
            return loan;
        }

        /// <summary>
        /// Returns a collection of all loans found within the database.
        /// </summary>
        /// <returns>A collection of all loans found within the database.</returns>
        public ICollection<Loan> GetAllLoans() {
            ICollection<Loan> allLoans = new List<Loan>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Loan", dbConn);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //create an Loan object for each record returned
                foreach (DataRow loanRow in dataTable.Rows) {
                    allLoans.Add(new Loan(
                        Convert.ToInt32(loanRow["ID"].ToString()),
                        DateUtil.ValidDate(loanRow["StartDate"].ToString()),
                        DateUtil.ValidDate(loanRow["DueDate"].ToString()),
                        loanRow["StudentID"].ToString(),
                        new Dictionary<string, DateTime>()));
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //Get the additional properties of the loans.
            foreach (Loan loan in allLoans) {
                loan.Assets = GetLoanAssets(loan.Id);
            }

            //return the loans
            return allLoans;
        }

        /// <summary>
        /// Update the details of an loan already stored within the database.
        /// </summary>
        /// <param name="loan">The new details of the loan to update. ID must remain the same as it is the PKey.</param>
        public void UpdateLoan(Loan loan) {
            //Ensure loan already exists
            if (!LoanExists(loan.Id)) {
                throw new Exception("Cannot update loan, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("PARAMETERS [StartDatePara] DATETIME, [DueDatePara] DATETIME; " +
                        "UPDATE [Loan] SET [StartDate] = [StartDatePara], [DueDate] = [DueDatePara], [StudentID] = ? WHERE ID = ?",
                        dbConn);
                    dbConn.Open();
                    oleDbCmd.Parameters.AddWithValue("StartDatePara", loan.StartDate);
                    oleDbCmd.Parameters.AddWithValue("DueDatePara", loan.DueDate);
                    oleDbCmd.Parameters.AddWithValue("StudentID", loan.StudentId);
                    oleDbCmd.Parameters.AddWithValue("ID", loan.Id);
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Loan not updated. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }

            //now store each of the loans assets
            foreach (KeyValuePair<string, DateTime> asset in loan.Assets) {
                StoreLoanAsset(loan.Id, asset);
            }
        }

        /// <summary>
        /// Delete data for a single loan from the database.
        /// </summary>
        /// <param name="id">The loan id of the loan to be deleted.</param>
        public void DeleteLoan(Loan loan) {

            //start by deleting all the assets of the loan
            foreach (KeyValuePair<string, DateTime> asset in loan.Assets) {
                DeleteLoanAsset(loan.Id, asset.Key);
            }

            //Ensure loan already exists
            if (!LoanExists(loan.Id)) {
                throw new Exception("Cannot delete loan, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM Loan WHERE ID = ?", dbConn);
                    oleDbCmd.Parameters.AddWithValue("ID", loan.Id);
                    dbConn.Open();
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Loan not deleted. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        //====================================================================
        //  END - CRUD METHODS
        //====================================================================

        //====================================================================
        //  UTILITY METHODS - USEFUL WHEN PERFORMING OTHER OPERATIONS
        //====================================================================

        /// <summary>
        /// Returns true if an loan exists with the specified Loan Id, otherwise returns false.
        /// </summary>
        /// <param name="id">The loan id to check for</param>
        /// <returns>True if an loan exists with the specified Id, otherwise false.</returns>
        public bool LoanExists(int id) {
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Loan WHERE (ID = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("ID", id);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //return true if at least one student was returned.
                return dataTable.Rows.Count > 0;
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        /// <summary>
        /// Returns true if a property with the specified name exists for the specified loan
        /// </summary>
        /// <param name="loanId">The loan whos property is being found</param>
        /// <param name="name">The name of the property to find</param>
        /// <returns>True if a property with the specified name exists for the specified loan</returns>
        public bool AssetExists(int loanId, string assetId) {
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM LoanAsset WHERE LoanId = ? AND AssetId = ?", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("LoanId", loanId);
                dAdapter.SelectCommand.Parameters.AddWithValue("AssetId", assetId);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //return true if at least one student was returned.
                return dataTable.Rows.Count > 0;
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        /// <summary>
        /// Stores an loan asset, creates a new loanasset if one does not already exist, otherwise it updates the existing one.
        /// </summary>
        /// <param name="loanId">The id of the loan this property is for</param>
        /// <param name="asset">The asset to create or update</param>
        private void StoreLoanAsset(int loanId, KeyValuePair<string, DateTime> asset) {
            if (AssetExists(loanId, asset.Key)) {
                if (asset.Value != DateTime.MinValue) {
                    try {
                        OleDbCommand oleDbCmd = new OleDbCommand("PARAMETERS [RetDatePara] DATETIME; " +
                            "UPDATE [LoanAsset] SET [ReturnDate] = [RetDatePara] WHERE LoanId = ? AND AssetId = ?",
                            dbConn);
                        dbConn.Open();

                        oleDbCmd.Parameters.AddWithValue("RetDatePara", asset.Value);
                        oleDbCmd.Parameters.AddWithValue("LoanId", loanId);
                        oleDbCmd.Parameters.AddWithValue("AssetId", asset.Key);
                        if (oleDbCmd.ExecuteNonQuery() < 1) {
                            throw new Exception("Asset '" + asset.Key + "' could not be updated for loan. An unknown error occured");
                        }
                    } catch (Exception e) {
                        throw e;
                    } finally {
                        dbConn.Close();
                    }
                }
            } else {
                //create new loan asset
                try {
                    //store the new loan
                    dbConn.Open();
                    OleDbCommand oleDbCmd = new OleDbCommand(
                        "INSERT INTO LoanAsset ([LoanId], [AssetId]) " +
                        "VALUES(@LoanId, @AssetId)",
                        dbConn);
                    oleDbCmd.Parameters.AddWithValue("@LoanId", loanId);
                    oleDbCmd.Parameters.AddWithValue("@AssetId", asset.Key);
                    //make sure the database changed
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Asset '" + asset.Key + "' could not be added to loan. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        /// <summary>
        /// Stores an loan asset, creates a new loanasset if one does not already exist, otherwise it updates the existing one.
        /// </summary>
        /// <param name="loanId">The id of the loan this property is for</param>
        /// <param name="assetid">The asset to create or update</param>
        private void StoreLoanAsset(int loanId, string assetId) {
            StoreLoanAsset(loanId, new KeyValuePair<string,DateTime>(assetId, DateTime.MinValue));
        }

        private Dictionary<string, DateTime> GetLoanAssets(int loanId) {
            Dictionary<string, DateTime> assets = new Dictionary<string, DateTime>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM LoanAsset WHERE (LoanId = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("LoanId", loanId);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //create a key value pair for each record returned
                foreach (DataRow loanRow in dataTable.Rows) {
                    assets.Add(
                        loanRow["AssetId"].ToString(),
                        DateUtil.ValidDate(loanRow["ReturnDate"].ToString())
                        );
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
            return assets;
        }

        /// <summary>
        /// Delete loan properties for a single loan.
        /// </summary>
        /// <param name="loanId">The loan id of the loan whos property is to be deleted</param>
        /// <param name="name">The name of the property to be deleted</param>
        public void DeleteLoanAsset(int loanId, string assetId) {
            //Ensure loan property already exists
            if (!AssetExists(loanId, assetId)) {
                //throw new Exception("Cannot delete property, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM LoanAsset WHERE LoanId = ? AND AssetId = ?", dbConn);
                    oleDbCmd.Parameters.AddWithValue("LoanId", loanId);
                    oleDbCmd.Parameters.AddWithValue("AssetId", assetId);
                    dbConn.Open();
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        //throw new Exception("Property not deleted. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        /// <summary>
        /// Checks if a certain asset is currently being loaned out, if it's not returned true, otherwise returns false.
        /// </summary>
        /// <param name="assetId">The asset whos availability is to be checked</param>
        /// <returns>True if the asset isn't currently on loan.</returns>
        public bool IsLoanAssetAvailable(string assetId){
            List<DateTime> returnDates = new List<DateTime>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM LoanAsset WHERE [AssetId] = ?", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("AssetId", assetId);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //create a key value pair for each record returned
                foreach (DataRow loanRow in dataTable.Rows) {
                    returnDates.Add(
                        DateUtil.ValidDate(loanRow["ReturnDate"].ToString())
                        );
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //Check that all instance of this loan item have been returned.
            foreach (DateTime date in returnDates) {
                //If any of the return dates are equal to the MinValue, then they haven't been returned,
                //  so this asset is already on loan.
                if (date == DateTime.MinValue) {
                    return false;
                }
            }
            //getting to here means all loan assets for the specified asset have been return, the asset is available.
            return true;
        }

        //====================================================================
        //  END - UTILITY METHODS
        //====================================================================

    }
}
